﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using br.com.SistemaChamados.Model.Common;
using br.com.SistemaChamados;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.Enum;
using System.Data;

/// <summary>
/// Summary description for CityDAO
/// </summary>
/// 
namespace br.com.SistemaChamados.DAO
{
    public class CityDAO
    {
        private static string SQL_ADD_CITY = "INSERT INTO TBL_CITY (TBL_CITYDESCRIPTION,TBL_STATEID) VALUES (@citydescription,@stateid)";
        private static string SQL_LIST_CITY = "SELECT * FROM TBL_CITY";
        private static string SQL_LIST_CITY_STATE = "SELECT * FROM TBL_CITY WHERE TBL_STATEID = @idstate";
        private static string SQL_SELECT_CITY = "SELECT * FROM TBL_CITY WHERE TBL_CITYID = @idcity";
        private static string SQL_DELETE_CITY = "DELETE FROM TBL_CITY WHERE TBL_CITYID = @idcity";
        private static string SQL_ALTER_CITY = "UPDATE TBL_CITY SET TBL_CITYDESCRIPTION=@cityDescription, TBL_STATEID=@stateId WHERE TBL_CITYID=@cityId ";

        public EnumGenericState updateCity(City city)
        {
            EnumGenericState resultState = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_ALTER_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@cityDescription", city.Description));
                command.Parameters.Add(Mapped.Parameter("@stateId", city.State.Code));
                command.Parameters.Add(Mapped.Parameter("@cityId", city.Id));
                command.ExecuteNonQuery();
                resultState = EnumGenericState.GENERIC_STATE_SUCCESS;
                return resultState;

            }
            catch (Exception ex)
            {
                resultState = EnumGenericState.GENERIC_STATE_FAILED;
                return resultState;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            
        }
        public EnumGenericState deleteCity(City city)
        {
            EnumGenericState resultState = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_CITY,conn);
                command.Parameters.Add(Mapped.Parameter("@idcity",city.Id));
                command.ExecuteNonQuery();
                resultState = EnumGenericState.GENERIC_STATE_SUCCESS;
                return resultState;

            }
            catch (Exception ex)
            {
                resultState = EnumGenericState.GENERIC_STATE_FAILED;
                return resultState;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<City> listCity()
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            City city = null;
            List<City> listCity = null;
            
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_LIST_CITY, conn);
                reader = command.ExecuteReader();
                listCity = new List<City>();
                State state = null;

                while (reader.Read())
                {
                    city = new City();
                    state = new State();
                    city.Id = Convert.ToInt32(reader["TBL_CITYID"]);
                    city.Description = Convert.ToString(reader["TBL_CITYDESCRIPTION"]);
                    state.Code = Convert.ToInt32(reader["TBL_STATEID"]);
                    city.State = state;
                    listCity.Add(city);
                }
                return listCity;
            }
            catch (Exception ex)
            {
                listCity = null;
                return null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<City> listCityByState(State state)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            City city = null;
            List<City> listCity = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_LIST_CITY_STATE, conn);
                command.Parameters.Add(Mapped.Parameter("@idstate", state.Code));
                reader = command.ExecuteReader();
                listCity = new List<City>();     

                while (reader.Read())
                {
                    city = new City();
                    city.Id = Convert.ToInt32(reader["TBL_CITYID"]);
                    city.Description = Convert.ToString(reader["TBL_CITYDESCRIPTION"]);
                    city.State = state;
                    listCity.Add(city);
                }
                return listCity;
            }
            catch (Exception ex)
            {
                listCity = null;
                return null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public City SelectCity(City city)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            City newCity = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_SELECT_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@idcity", city.Id));
                reader = command.ExecuteReader();
                State state = null;

                while (reader.Read())
                {
                    newCity = new City();
                    state = new State();
                    newCity.Id = Convert.ToInt32(city.Id);
                    newCity.Description = Convert.ToString(reader["TBL_CITYDESCRIPTION"]);
                    state.Code = Convert.ToInt32(reader["TBL_STATEID"]);
                    newCity.State = state;
                }
                return newCity;
            }
            catch (Exception ex)
            {
                newCity = null;
                return null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }


        public EnumGenericState addCity(City city)
        {
            EnumGenericState resultState = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_ADD_CITY, conn);
                command.Parameters.Add(Mapped.Parameter("@citydescription", city.Description));
                command.Parameters.Add(Mapped.Parameter("@stateid", city.State.Code));
                command.ExecuteNonQuery();
                resultState = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                resultState = EnumGenericState.GENERIC_STATE_FAILED;
                city = null;
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return resultState;
        }
        
        public CityDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }
    }
}